package com.sunda.spmsoversea.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.sunda.spmsoversea.dto.OverseaDeliveryNotePageDTO;
import com.sunda.spmsoversea.entity.OverseaDeliveryNote;
import com.sunda.spmsoversea.entity.OverseaWhsIn;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 海外仓交货单表 Mapper 接口
 * </p>
 *
 * @author Wayne
 * @since 2021-10-22
 */
@Mapper
public interface OverseaDeliveryNoteMapper extends BaseMapper<OverseaDeliveryNote> {

    @Select("<script>" +
            "SELECT ODN.UUID_DELIVERY_NOTE AS \"uuidDeliveryNote\",\n" +
            "       ODN.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODN.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       ODN.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       ODN.DELIVERY_TYPE AS \"deliveryType\",\n" +
            "       ODN.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       ODN.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       ODN.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       ODN.REMARKS AS \"remarks\",\n" +
            "       ODN.COMMENTS AS \"comments\",\n" +
            "       ODN.POSTING_DATE AS \"postingDate\",\n" +
            "       ODN.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       ODN.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       ODN.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       ODN.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       ODN.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       ODN.CANCEL_UNDER_POSTING_DATE AS \"cancelUnderPostingDate\",\n" +
            "       ODN.CANCEL_UNDER_VOUCHER_NUMBER AS \"cancelUnderVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_NUMBER AS \"underVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_YEAR AS \"underVoucherYear\",\n" +
            "       ODN.UNDER_POSTING_DATE AS \"underPostingDate\",\n" +
            "       ODN.SAP_REMARK AS \"sapRemark\",\n" +
            "       ODN.DATA_VERSION AS \"dataVersion\",\n" +
            "       ODN.OA_WORKFLOW AS \"oaWorkflow\",\n" +
            "       ODN.OA_BIANHAO AS \"oaBianhao\",\n" +
            "       ODN.OA_REMARK AS \"oaRemark\",\n" +
            "       ODN.SUBMIT_OA_DATE AS \"submitOaDate\",\n" +
            "       ODN.OA_APPROVAL_DATE AS \"oaApprovalDate\",\n" +
            "       ODN.COST_CENTER AS \"costCenter\",\n" +
            "       OPL.MERCHANDISER AS \"merchandiser\",\n" +
            "       OPL.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       OPL.FROM_PORT AS \"fromPort\",\n" +
            "       OPL.TO_PORT AS \"toPort\",\n" +
            "       ODN.ACTUAL_WERKS_RECEIVE AS \"actualWerksReceive\",\n" +
            "       ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE AS \"actualWhsLocationCodeReceive\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "    LEFT JOIN OVERSEA_PACKAGE_LIST OPL ON ODN.SAP_PACKAGE_LIST = OPL.SAP_PACKAGE_LIST\n" +
            "WHERE ODN.SPMS_STATUS != '12' " +
            "  <when test='dto.sapDeliveryNote!=null and dto.sapDeliveryNote != \"\"'> AND ODN.SAP_DELIVERY_NOTE = #{dto.sapDeliveryNote}</when>" +
            "  <when test='dto.sapPackageList!=null and dto.sapPackageList != \"\"'> AND ODN.SAP_PACKAGE_LIST = #{dto.sapPackageList}</when>" +
            "  <when test='dto.cabinetNumber!=null and dto.cabinetNumber != \"\"'> AND ODN.CABINET_NUMBER = #{dto.cabinetNumber}</when>" +
            "  <when test='dto.spmsStatus!=null and dto.spmsStatus != \"\"'> AND ODN.SPMS_STATUS = #{dto.spmsStatus}</when>" +
            "  <when test='dto.werksReceive!=null and dto.werksReceive != \"\"'> AND ODN.WERKS_RECEIVE = #{dto.werksReceive}</when>" +
            "  <when test='dto.werksDelivery!=null and dto.werksDelivery != \"\"'> AND ODN.WERKS_DELIVERY = #{dto.werksDelivery}</when>" +
            "ORDER BY TO_NUMBER(ODN.SPMS_STATUS) ASC, ODN.SAP_DELIVERY_NOTE ASC " +
            "</script>")
    /** 获取交货单表头分页列表 */
    List<Map<String, Object>> getOverseaDeliveryNoteIpage(IPage page, @Param("dto") OverseaDeliveryNotePageDTO dto);

    @Select("<script>" +
            "SELECT ODN.UUID_DELIVERY_NOTE AS \"uuidDeliveryNote\",\n" +
            "       ODN.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODN.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       ODN.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       ODN.DELIVERY_TYPE AS \"deliveryType\",\n" +
            "       ODN.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       ODN.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       ODN.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       ODN.REMARKS AS \"remarks\",\n" +
            "       ODN.COMMENTS AS \"comments\",\n" +
            "       ODN.POSTING_DATE AS \"postingDate\",\n" +
            "       ODN.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       ODN.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       ODN.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       ODN.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       ODN.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       ODN.CANCEL_UNDER_POSTING_DATE AS \"cancelUnderPostingDate\",\n" +
            "       ODN.CANCEL_UNDER_VOUCHER_NUMBER AS \"cancelUnderVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_NUMBER AS \"underVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_YEAR AS \"underVoucherYear\",\n" +
            "       ODN.UNDER_POSTING_DATE AS \"underPostingDate\",\n" +
            "       ODN.SAP_REMARK AS \"sapRemark\",\n" +
            "       ODN.DATA_VERSION AS \"dataVersion\",\n" +
            "       ODN.OA_WORKFLOW AS \"oaWorkflow\",\n" +
            "       ODN.OA_BIANHAO AS \"oaBianhao\",\n" +
            "       ODN.OA_REMARK AS \"oaRemark\",\n" +
            "       ODN.SUBMIT_OA_DATE AS \"submitOaDate\",\n" +
            "       ODN.OA_APPROVAL_DATE AS \"oaApprovalDate\",\n" +
            "       ODN.COST_CENTER AS \"costCenter\",\n" +
            "       OPL.MERCHANDISER AS \"merchandiser\",\n" +
            "       OPL.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       OPL.FROM_PORT AS \"fromPort\",\n" +
            "       OPL.TO_PORT AS \"toPort\",\n" +
            "       ODN.ACTUAL_WERKS_RECEIVE AS \"actualWerksReceive\",\n" +
            "       ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE AS \"actualWhsLocationCodeReceive\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "    LEFT JOIN OVERSEA_PACKAGE_LIST OPL ON ODN.SAP_PACKAGE_LIST = OPL.SAP_PACKAGE_LIST\n" +
            "WHERE ODN.SPMS_STATUS != '12' " +
            "  <when test='dto.sapDeliveryNote!=null and dto.sapDeliveryNote != \"\"'> AND ODN.SAP_DELIVERY_NOTE = #{dto.sapDeliveryNote}</when>" +
            "  <when test='dto.sapPackageList!=null and dto.sapPackageList != \"\"'> AND ODN.SAP_PACKAGE_LIST = #{dto.sapPackageList}</when>" +
            "  <when test='dto.cabinetNumber!=null and dto.cabinetNumber != \"\"'> AND ODN.CABINET_NUMBER = #{dto.cabinetNumber}</when>" +
            "  <when test='spmsStatusList!=null'> " +
            "     AND ODN.SPMS_STATUS IN " +
            "         <foreach item='item' index='index' collection='spmsStatusList' open='(' separator=',' close=')'>\n" +
            "            #{item} \n" +
            "         </foreach>" +
            "  </when>\n" +
            "  <when test='dto.werksReceive!=null and dto.werksReceive != \"\"'> AND ODN.WERKS_RECEIVE = #{dto.werksReceive}</when>" +
            "  <when test='dto.werksDelivery!=null and dto.werksDelivery != \"\"'> AND ODN.WERKS_DELIVERY = #{dto.werksDelivery}</when>" +
            "ORDER BY TO_NUMBER(ODN.SPMS_STATUS) ASC, ODN.SAP_DELIVERY_NOTE ASC " +
            "</script>")
    /** 获取交货单表头分页列表，PDA多状态查询 */
    List<Map<String, Object>> getOverseaDeliveryNoteIpagePda(IPage page, @Param("dto") OverseaDeliveryNotePageDTO dto, @Param("spmsStatusList") List<String> spmsStatusList);

    @Select("SELECT ODN.UUID_DELIVERY_NOTE AS \"uuidDeliveryNote\",\n" +
            "       ODN.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODN.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       ODN.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       ODN.DELIVERY_TYPE AS \"deliveryType\",\n" +
            "       ODN.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       ODN.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       ODN.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       ODN.REMARKS AS \"remarks\",\n" +
            "       ODN.COMMENTS AS \"comments\",\n" +
            "       ODN.POSTING_DATE AS \"postingDate\",\n" +
            "       ODN.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       ODN.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       ODN.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       ODN.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       ODN.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       ODN.CANCEL_UNDER_POSTING_DATE AS \"cancelUnderPostingDate\",\n" +
            "       ODN.CANCEL_UNDER_VOUCHER_NUMBER AS \"cancelUnderVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_NUMBER AS \"underVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_YEAR AS \"underVoucherYear\",\n" +
            "       ODN.UNDER_POSTING_DATE AS \"underPostingDate\",\n" +
            "       ODN.SAP_REMARK AS \"sapRemark\",\n" +
            "       ODN.DATA_VERSION AS \"dataVersion\",\n" +
            "       ODN.OA_WORKFLOW AS \"oaWorkflow\",\n" +
            "       ODN.OA_BIANHAO AS \"oaBianhao\",\n" +
            "       ODN.OA_REMARK AS \"oaRemark\",\n" +
            "       ODN.SUBMIT_OA_DATE AS \"submitOaDate\",\n" +
            "       ODN.OA_APPROVAL_DATE AS \"oaApprovalDate\",\n" +
            "       ODN.COST_CENTER AS \"costCenter\",\n" +
            "       OPL.MERCHANDISER AS \"merchandiser\",\n" +
            "       OPL.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       OPL.FROM_PORT AS \"fromPort\",\n" +
            "       OPL.TO_PORT AS \"toPort\",\n" +
            "       ODN.ACTUAL_WERKS_RECEIVE AS \"actualWerksReceive\",\n" +
            "       ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE AS \"actualWhsLocationCodeReceive\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "    LEFT JOIN OVERSEA_PACKAGE_LIST OPL ON ODN.SAP_PACKAGE_LIST = OPL.SAP_PACKAGE_LIST\n" +
            "WHERE ODN.SAP_DELIVERY_NOTE = #{sapDeliveryNote} " )
    Map<String, Object> getOverseaDnHead(@Param("sapDeliveryNote") String sapDeliveryNote);

    @Select("SELECT ODND.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODND.SAP_DELIVERY_NOTE_ITEM AS \"sapDeliveryNoteItem\",\n" +
            "       ODND.MATERIAL_NO AS \"materialNo\",\n" +
            "       ODND.RECEIVABLE_QTY_BASIC_UNIT AS \"receivableQtyBasicUnit\",\n" +
            "       ODND.RECEIVABLE_QTY_UNIT_SALES AS \"receivableQtyUnitSales\",\n" +
            "       NVL(RE.ACTUAL_QTY_BASIC_UNIT, 0) AS \"actualQtyBasicUnit\",\n" +
            "       NVL(RE.ACTUAL_QTY_UNIT_SALES, 0) AS \"actualQtyUnitSales\",\n" +
            "       SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "       NVL(SAP.UNIT_SALES, SAP.BASIC_UNIT) AS \"unitSales\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       NVL(SAP.DENOMINATOR, 1) AS \"denominator\",\n" +
            "       NVL(SAP.NUMERATOR, 1) AS \"numerator\",\n" +
            "       (CASE WHEN NVL(RE.ACTUAL_QTY_BASIC_UNIT, 0) - ODND.RECEIVABLE_QTY_BASIC_UNIT > 0\n" +
            "           THEN NVL(RE.ACTUAL_QTY_BASIC_UNIT, 0) - ODND.RECEIVABLE_QTY_BASIC_UNIT\n" +
            "           ELSE 0 END) AS \"overchargedQtyBasicUnit\",\n" +
            "       (CASE WHEN ODND.RECEIVABLE_QTY_BASIC_UNIT - NVL(RE.ACTUAL_QTY_BASIC_UNIT, 0) > 0\n" +
            "           THEN ODND.RECEIVABLE_QTY_BASIC_UNIT - NVL(RE.ACTUAL_QTY_BASIC_UNIT, 0)\n" +
            "           ELSE 0 END) AS \"underchargedQtyBasicUnit\",\n" +
            "       (CASE WHEN NVL(RE.ACTUAL_QTY_UNIT_SALES, 0) - ODND.RECEIVABLE_QTY_UNIT_SALES > 0\n" +
            "           THEN NVL(RE.ACTUAL_QTY_UNIT_SALES, 0) - ODND.RECEIVABLE_QTY_UNIT_SALES\n" +
            "           ELSE 0 END) AS \"overchargedQtyUnitSales\",\n" +
            "       (CASE WHEN ODND.RECEIVABLE_QTY_UNIT_SALES - NVL(RE.ACTUAL_QTY_UNIT_SALES, 0) > 0\n" +
            "           THEN ODND.RECEIVABLE_QTY_UNIT_SALES - NVL(RE.ACTUAL_QTY_UNIT_SALES, 0)\n" +
            "           ELSE 0 END) AS \"underchargedQtyUnitSales\",\n" +
            "       ODND.REMARKS AS \"remarks\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE_DTL ODND\n" +
            "    LEFT JOIN (\n" +
            "        SELECT OWI.SAP_DELIVERY_NOTE,\n" +
            "               OWID.SAP_DELIVERY_NOTE_ITEM,\n" +
            "               OWID.MATERIAL_NO,\n" +
            "               SUM(NVL(OWID.ACTUAL_QTY_BASIC_UNIT, 0)) AS ACTUAL_QTY_BASIC_UNIT,\n" +
            "               SUM(NVL(OWID.ACTUAL_QTY_UNIT_SALES, 0)) AS ACTUAL_QTY_UNIT_SALES\n" +
            "        FROM OVERSEA_WHS_IN_DTL OWID\n" +
            "            LEFT JOIN OVERSEA_WHS_IN OWI ON OWID.UUID_WHS_IN = OWI.UUID_WHS_IN\n" +
            "        WHERE OWI.SPMS_STATUS IN ('3', '4', '5') AND OWI.SAP_DELIVERY_NOTE = #{sapDeliveryNote}\n" +
            "        GROUP BY OWI.SAP_DELIVERY_NOTE, OWID.SAP_DELIVERY_NOTE_ITEM, OWID.MATERIAL_NO\n" +
            "        ) RE ON ODND.SAP_DELIVERY_NOTE = RE.SAP_DELIVERY_NOTE\n" +
            "                    AND ODND.SAP_DELIVERY_NOTE_ITEM = RE.SAP_DELIVERY_NOTE_ITEM\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON ODND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE ODND.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ORDER BY TO_NUMBER(ODND.SAP_DELIVERY_NOTE_ITEM) ASC ")
    List<Map<String, Object>> getOverseaDnOverOrUnderCharged(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 根据交货单号获取进行中状态的入库任务；入库任务状态
     * 入库任务状态(默认0待入库;1入库中;2入库完成;3审批通过;4提交SAP成功;5提交SAP失败;6已撤销;7关闭;)
     * */
    @Select("SELECT OWI.UUID_WHS_IN AS \"uuidWhsIn\",\n" +
            "       OWI.WHS_IN_NO AS \"whsInNo\",\n" +
            "       OWI.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       OWI.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       OWI.WERKS AS \"werks\",\n" +
            "       OWI.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OWI.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OWI.CREATE_DATE AS \"createDate\"\n" +
            "FROM OVERSEA_WHS_IN OWI\n" +
            "WHERE OWI.SAP_DELIVERY_NOTE = #{sapDeliveryNote} \n" +
            "  AND OWI.SPMS_STATUS != '4'\n" +
            "  AND OWI.SPMS_STATUS != '7' ")
    List<OverseaWhsIn> getDoingTask(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 根据交货单号和行号，获取交货单一行明细信息传输OA */
    @Select("SELECT ODND.MATERIAL_NO AS \"materialNo\",\n" +
            "       SAP.MATERIAL_ZH_DESC AS \"materialZhDesc\",\n" +
            "       SAP.MATERIAL_EN_DESC AS \"materialEnDesc\",\n" +
            "       SAP.BASIC_UNIT AS \"basicUnit\",\n" +
            "       ODND.COLOR_NO AS \"colorNo\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE_DTL ODND\n" +
            "    LEFT JOIN MATERIAL_SAP SAP ON ODND.MATERIAL_NO = SAP.MATERIAL_NO\n" +
            "WHERE ODND.SAP_DELIVERY_NOTE = #{sapDeliveryNote} AND ODND.SAP_DELIVERY_NOTE_ITEM = #{sapDeliveryNoteItem} ")
    Map<String, Object> itemInfo(@Param("sapDeliveryNote") String sapDeliveryNote,
                                 @Param("sapDeliveryNoteItem") String sapDeliveryNoteItem);

    /** 交货单多收少收请求SAP信息结构，盘盈盘亏接口;701-盘盈，702-盘亏 */
    @Select("SELECT REPLACE(ODN.ARRIVAL_DATE, '-', '') AS \"bldat\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"budat\",\n" +
            "       #{operateType} AS \"bwart\",\n" +
            "       ODN.ACTUAL_WERKS_RECEIVE AS \"werks\",\n" +
            "       ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE AS \"lgort\",\n" +
            "       ODN.SAP_DELIVERY_NOTE || ODN.REMARKS AS \"bktxt\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "WHERE ODN.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHead(@Param("sapDeliveryNote") String sapDeliveryNote,
                                 @Param("operateType") String operateType,
                                 @Param("postingDate") String postingDate);

    /** 获取有效入库任务判断交货单是否能退回到SAP可能新状态，即入库任务状态 不等于7 */
    @Select("SELECT OWI.UUID_WHS_IN AS \"uuidWhsIn\",\n" +
            "       OWI.WHS_IN_NO AS \"whsInNo\",\n" +
            "       OWI.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       OWI.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       OWI.WERKS AS \"werks\",\n" +
            "       OWI.WHS_LOCATION_CODE AS \"whsLocationCode\",\n" +
            "       OWI.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       OWI.CREATE_DATE AS \"createDate\"\n" +
            "FROM OVERSEA_WHS_IN OWI\n" +
            "WHERE OWI.SAP_DELIVERY_NOTE = #{sapDeliveryNote} \n" +
            "  AND OWI.SPMS_STATUS != '7' ")
    List<OverseaWhsIn> getValidateTask(@Param("sapDeliveryNote") String sapDeliveryNote);

    /** 收货多收少收请求SAP结构，成本中心接口；多收对应移动类型Z16，少收对应移动类型Z15； */
    @Select("SELECT ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE AS \"lgort\",\n" +
            "       ODN.ACTUAL_WERKS_RECEIVE AS \"werks\",\n" +
            "       REPLACE(ODN.ARRIVAL_DATE, '-', '') AS \"bldat\",\n" +
            "       REPLACE(#{postingDate}, '-', '') AS \"budat\",\n" +
            "       #{operateType} AS \"bwart\",\n" +
            "       ODN.SAP_DELIVERY_NOTE || ODN.REMARKS AS \"bktxt\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "WHERE SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHeadAnomalyPost(@Param("sapDeliveryNote") String sapDeliveryNote,
                                            @Param("operateType") String operateType,
                                            @Param("postingDate") String postingDate);

    /** 收货少收请求SAP结构，成本中心接口SAP撤销 */
    @Select("SELECT ODN.UNDER_VOUCHER_NUMBER AS \"iMblnr\",\n" +
            "       ODN.UNDER_VOUCHER_YEAR AS \"iMjahr\",\n" +
            "       REPLACE(#{cancelUnderPostingDate}, '-', '') AS \"iBudat\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "WHERE ODN.SAP_DELIVERY_NOTE = #{sapDeliveryNote} ")
    Map<String, Object> dataHeadCancelAnomalyPost(@Param("sapDeliveryNote") String sapDeliveryNote,
                                                  @Param("cancelUnderPostingDate") String cancelUnderPostingDate);

    @Select("<script>" +
            "SELECT ODN.UUID_DELIVERY_NOTE AS \"uuidDeliveryNote\",\n" +
            "       ODN.SAP_DELIVERY_NOTE AS \"sapDeliveryNote\",\n" +
            "       ODN.SAP_PACKAGE_LIST AS \"sapPackageList\",\n" +
            "       ODN.CABINET_NUMBER AS \"cabinetNumber\",\n" +
            "       ODN.DELIVERY_TYPE AS \"deliveryType\",\n" +
            "       ODN.SPMS_STATUS AS \"spmsStatus\",\n" +
            "       ODN.WERKS_RECEIVE AS \"werksReceive\",\n" +
            "       ODN.WERKS_DELIVERY AS \"werksDelivery\",\n" +
            "       ODN.ARRIVAL_DATE AS \"arrivalDate\",\n" +
            "       ODN.REMARKS AS \"remarks\",\n" +
            "       ODN.COMMENTS AS \"comments\",\n" +
            "       ODN.POSTING_DATE AS \"postingDate\",\n" +
            "       ODN.SAP_VOUCHER_NUMBER AS \"sapVoucherNumber\",\n" +
            "       ODN.SAP_VOUCHER_YEAR AS \"sapVoucherYear\",\n" +
            "       ODN.CANCEL_POSTING_DATE AS \"cancelPostingDate\",\n" +
            "       ODN.CANCEL_VOUCHER_NUMBER AS \"cancelVoucherNumber\",\n" +
            "       ODN.CANCEL_VOUCHER_YEAR AS \"cancelVoucherYear\",\n" +
            "       ODN.CANCEL_UNDER_POSTING_DATE AS \"cancelUnderPostingDate\",\n" +
            "       ODN.CANCEL_UNDER_VOUCHER_NUMBER AS \"cancelUnderVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_NUMBER AS \"underVoucherNumber\",\n" +
            "       ODN.UNDER_VOUCHER_YEAR AS \"underVoucherYear\",\n" +
            "       ODN.UNDER_POSTING_DATE AS \"underPostingDate\",\n" +
            "       ODN.SAP_REMARK AS \"sapRemark\",\n" +
            "       ODN.DATA_VERSION AS \"dataVersion\",\n" +
            "       ODN.COST_CENTER AS \"costCenter\",\n" +
            "       OPL.MERCHANDISER AS \"merchandiser\",\n" +
            "       OPL.CABINET_MODEL AS \"cabinetModel\",\n" +
            "       OPL.FROM_PORT AS \"fromPort\",\n" +
            "       OPL.TO_PORT AS \"toPort\",\n" +
            "       ODN.ACTUAL_WERKS_RECEIVE AS \"actualWerksReceive\",\n" +
            "       ODN.ACTUAL_WHS_LOCATION_CODE_RECEIVE AS \"actualWhsLocationCodeReceive\"\n" +
            "FROM OVERSEA_DELIVERY_NOTE ODN\n" +
            "    LEFT JOIN OVERSEA_PACKAGE_LIST OPL ON ODN.SAP_PACKAGE_LIST = OPL.SAP_PACKAGE_LIST\n" +
            "WHERE ODN.SPMS_STATUS NOT IN ('0', '11', '12') " +
            "  <when test='werks!=null and werks != \"\"'> AND ODN.WERKS_RECEIVE = #{werks}</when>" +
            "   AND TO_NUMBER(TO_DATE(TO_CHAR(SYSDATE, 'yyyy-MM-dd'),'yyyy-MM-dd') - " +
            "                 TO_DATE(ODN.ARRIVAL_DATE, 'yyyy-MM-dd')) >= 14 " +
            "   ORDER BY ODN.WERKS_RECEIVE, TO_NUMBER(ODN.SPMS_STATUS) ASC, ODN.SAP_DELIVERY_NOTE ASC" +
            "</script>")
    List<Map<String, Object>> getOverdueDeliveryNote(@Param("werks") String werks);
}
